﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web.Security;
using System.Xml.Linq;

namespace FranceTriple.Dal
{
    public class ForumTopicView : IDisposable
    {
        private FranceTriple.Dal.Model1Container ctx = new Model1Container();
        private bool disposedValue = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposedValue)
            {
                if (disposing)
                {
                    ctx.Dispose();
                }
            }
            this.disposedValue = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        // 20 Derniers Posts
        public List<TopicResult> GetForumLast20Post(String AnonymousID, String requesterTimeZone)
        {
            List<TopicResult> results = new List<TopicResult>();

            try
            {
                List<Int32> ForumIdAllowed = UserAccessDal.ForumIdAllowed(AnonymousID);

                using (FranceTriple.Dal.Model1Container ctx = new Model1Container())
                {
                    var query = (from t in ctx.vw_ForumLastTopic
                                 where ForumIdAllowed.Contains(t.ForumId)
                                 orderby t.LastPostDate descending
                                 select new TopicResult()
                                 {
                                     Id = t.Id,
                                     Label = t.Label,
                                     Author = t.Author,
                                     NumberHasBeenSeen = t.NumberHasBeenSeen,
                                     IsUnlocked = t.IsUnlocked,
                                     OrderForumTopic = t.OrderForumTopic,
                                     CreationDateForumTopic = t.CreationDateForumTopic,
                                     NumberOfPost = t.NumberOfPost ?? 0,
                                     LastPostDate = (t.LastPostDate ?? new DateTime()),//Todo: conversion du fuseau horaire: .ToUserTimeZone(requesterTimeZone),
                                     LastPostAuthor = (from p in ctx.ForumPostSet where (p.Id == t.LastPostId) select p.PostMessage.Author).FirstOrDefault(),
                                     LastPostTopicId = t.Id,
                                     LastPostId = t.LastPostId ?? 0,
                                     ForumCategoryId = t.ForumCategoryId,
                                     ForumId = t.ForumId
                                 }).Take(10).ToList();

                    //Todo: conversion du fuseau horaire: .ToUserTimeZone(requesterTimeZone)
                    // La seule raison de cette boucle est l'absence de cette fonction dans le modele
                    foreach (var t in query)
                    {
                        results.Add(new TopicResult()
                        {
                            Id = t.Id,
                            Label = t.Label,
                            NumberOfPost = t.NumberOfPost,
                            Author = t.Author,
                            NumberHasBeenSeen = t.NumberHasBeenSeen,
                            IsUnlocked = t.IsUnlocked,
                            OrderForumTopic = t.OrderForumTopic,
                            CreationDateForumTopic = t.CreationDateForumTopic,
                            LastPostDate = t.LastPostDate.ToUserTimeZone(requesterTimeZone),
                            LastPostAuthor = t.LastPostAuthor,
                            LastPostTopicId = t.Id,
                            LastPostId = t.LastPostId,
                            ForumCategoryId = t.ForumCategoryId,
                            ForumId = t.ForumId
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                LoggingManager.Logger.Log(LoggingCategory.Error, ex.Message);
                throw new DalException(String.Format("RetrieveError".ToLocalizedString(), "GetForumLast20Post", ex.Message), ex) { EntityType = typeof(List<TopicResult>) };
            }
            return results;
        }

        // Posts créés en dernier
        public DataSet GetLastPostCreateXmlEntities(String AnonymousID, String requesterTimeZone)
        {
            XElement xelem = null;

            try
            {
                List<Int32> ForumIdAllowed = UserAccessDal.ForumIdAllowed(AnonymousID);

                using (FranceTriple.Dal.Model1Container ctx = new Model1Container())
                {
                    var query = (from t1 in
                                     (from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumCategory")
                                      where ForumIdAllowed.Contains(t.ForumCategory.Forum.Id)
                                      orderby t.ForumPost.Max(post => post.PostMessage.CreationDate) descending
                                      select t).ToList()
                                 select new XElement("Topic",
                                     new XAttribute("Id", t1.Id),
                                     new XAttribute("Label", t1.LabelForumTopic),
                                     new XAttribute("NumberOfPost", t1.ForumPost.Count),
                                     new XAttribute("Author", t1.AuthorForumTopic),
                                     new XAttribute("NumberHasBeenSeen", t1.NumberHasBeenSeenForumTopic),
                                     new XAttribute("IsUnlocked", t1.IsUnlocked),
                                     new XAttribute("OrderForumTopic", t1.OrderForumTopic),
                                     new XAttribute("CreationDateForumTopic", t1.CreationDateForumTopic.ToUserTimeZone(requesterTimeZone).ToShortDateString()),
                                     new XAttribute("ForumCategoryId", t1.ForumCategory.Id),
                                     from p in t1.ForumPost
                                     where t1.ForumPost.Max(t2 => t2.PostMessage.CreationDate) == p.PostMessage.CreationDate
                                     select new XElement("LastPost",
                                         new XAttribute("LastPostDate", p.PostMessage.CreationDate.ToUserTimeZone(requesterTimeZone).ToString()),
                                         new XAttribute("LastPostAuthor", p.PostMessage.Author),
                                         new XAttribute("LastPostTopicId", p.ForumTopic.Id.ToString()),
                                         new XAttribute("LastPostId", p.Id.ToString()),
                                         new XAttribute("ForumCategoryId", t1.ForumCategory.Id)
                                         )
                                         ));
                    xelem = new XElement("o", query);
                }
            }
            catch (Exception ex)
            {
                LoggingManager.Logger.Log(LoggingCategory.Error, ex.Message);
                throw new DalException(String.Format("RetrieveError".ToLocalizedString(), "GetXmlEntities", ex.Message), ex) { EntityType = typeof(Forum[]) };
            }
            return this.xmlResultToDataSet(xelem.ToString());
        }
        // Topic d'une category
        public DataSet GetXmlEntities(Int32 ForumCategoryId, String AnonymousID, String requesterTimeZone)
        {
            XElement xelem = null;

            try
            {
                using (FranceTriple.Dal.Model1Container ctx = new Model1Container())
                {
                    List<Int32> ForumIdAllowed = UserAccessDal.ForumIdAllowed(AnonymousID);

                    var query = (from t1 in
                                     (from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumCategory")
                                      where t.ForumCategory.Id == ForumCategoryId & ForumIdAllowed.Contains(t.ForumCategory.Forum.Id)
                                      orderby t.OrderForumTopic descending, t.CreationDateForumTopic descending
                                      select t).ToList()
                                 select new XElement("Topic",
                                     new XAttribute("Id", t1.Id),
                                     new XAttribute("Label", t1.LabelForumTopic),
                                     new XAttribute("NumberOfPost", t1.ForumPost.Count),
                                     new XAttribute("Author", t1.AuthorForumTopic),
                                     new XAttribute("NumberHasBeenSeen", t1.NumberHasBeenSeenForumTopic),
                                     new XAttribute("IsUnlocked", t1.IsUnlocked),
                                     new XAttribute("OrderForumTopic", t1.OrderForumTopic),
                                     new XAttribute("CreationDateForumTopic", t1.CreationDateForumTopic.ToUserTimeZone(requesterTimeZone).ToShortDateString()),
                                     new XAttribute("ForumCategoryId", t1.ForumCategory.Id),
                                     from p in t1.ForumPost
                                     where t1.ForumPost.Max(t2 => t2.PostMessage.CreationDate) == p.PostMessage.CreationDate
                                     select new XElement("LastPost",
                                         new XAttribute("LastPostDate", p.PostMessage.CreationDate.ToUserTimeZone(requesterTimeZone).ToString()),
                                         new XAttribute("LastPostAuthor", p.PostMessage.Author),
                                         new XAttribute("LastPostTopicId", p.ForumTopic.Id.ToString()),
                                         new XAttribute("LastPostId", p.Id.ToString()),
                                         new XAttribute("ForumCategoryId", t1.ForumCategory.Id)
                                                        )
                                         ));
                    xelem = new XElement("o", query);
                }
            }
            catch (Exception ex)
            {
                LoggingManager.Logger.Log(LoggingCategory.Error, ex.Message);
                throw new DalException(String.Format("RetrieveError".ToLocalizedString(), "GetXmlEntities", ex.Message), ex) { EntityType = typeof(Forum[]) };
            }
            return this.xmlResultToDataSet(xelem.ToString());
        }

        // Topics créés en dernier
        public DataSet GetLastTopicCreateXmlEntities(String AnonymousID, String requesterTimeZone)
        {
            XElement xelem = null;

            try
            {
                List<Int32> ForumIdAllowed = UserAccessDal.ForumIdAllowed(AnonymousID);

                using (FranceTriple.Dal.Model1Container ctx = new Model1Container())
                {
                    var query = (from t1 in
                                     (from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumCategory")
                                      where ForumIdAllowed.Contains(t.ForumCategory.Forum.Id)
                                      orderby t.CreationDateForumTopic descending
                                      select t).ToList()
                                 select new XElement("Topic",
                                     new XAttribute("Id", t1.Id),
                                     new XAttribute("Label", t1.LabelForumTopic),
                                     new XAttribute("NumberOfPost", t1.ForumPost.Count),
                                     new XAttribute("Author", t1.AuthorForumTopic),
                                     new XAttribute("NumberHasBeenSeen", t1.NumberHasBeenSeenForumTopic),
                                     new XAttribute("IsUnlocked", t1.IsUnlocked),
                                     new XAttribute("OrderForumTopic", t1.OrderForumTopic),
                                     new XAttribute("CreationDateForumTopic", t1.CreationDateForumTopic.ToUserTimeZone(requesterTimeZone).ToShortDateString()),
                                     new XAttribute("ForumCategoryId", t1.ForumCategory.Id),
                                     from p in t1.ForumPost
                                     where t1.ForumPost.Max(t2 => t2.PostMessage.CreationDate) == p.PostMessage.CreationDate
                                     select new XElement("LastPost",
                                         new XAttribute("LastPostDate", p.PostMessage.CreationDate.ToUserTimeZone(requesterTimeZone).ToString()),
                                         new XAttribute("LastPostAuthor", p.PostMessage.Author),
                                         new XAttribute("LastPostTopicId", p.ForumTopic.Id.ToString()),
                                         new XAttribute("LastPostId", p.Id.ToString()),
                                         new XAttribute("ForumCategoryId", t1.ForumCategory.Id)
                                         )
                                         ));
                    xelem = new XElement("o", query);
                }
            }
            catch (Exception ex)
            {
                LoggingManager.Logger.Log(LoggingCategory.Error, ex.Message);
                throw new DalException(String.Format("RetrieveError".ToLocalizedString(), "GetXmlEntities", ex.Message), ex) { EntityType = typeof(Forum[]) };
            }
            return this.xmlResultToDataSet(xelem.ToString());
        }

        //Topic sans réponse : 1 message et sujet non-vérouillé
        public DataSet GetOrphanXmlEntities(String AnonymousID, String requesterTimeZone)
        {
            XElement xelem = null;

            try
            {
                List<Int32> ForumIdAllowed = UserAccessDal.ForumIdAllowed(AnonymousID);

                using (FranceTriple.Dal.Model1Container ctx = new Model1Container())
                {
                    var query = (from t1 in
                                     (from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumCategory")
                                      where ForumIdAllowed.Contains(t.ForumCategory.Forum.Id)
                                      orderby t.CreationDateForumTopic descending
                                      where t.ForumPost.Count == 1 && t.IsUnlocked == true
                                      select t).ToList()
                                 select new XElement("Topic",
                                     new XAttribute("Id", t1.Id),
                                     new XAttribute("Label", t1.LabelForumTopic),
                                     new XAttribute("NumberOfPost", t1.ForumPost.Count),
                                     new XAttribute("Author", t1.AuthorForumTopic),
                                     new XAttribute("NumberHasBeenSeen", t1.NumberHasBeenSeenForumTopic),
                                     new XAttribute("IsUnlocked", t1.IsUnlocked),
                                     new XAttribute("OrderForumTopic", t1.OrderForumTopic),
                                     new XAttribute("CreationDateForumTopic", t1.CreationDateForumTopic.ToUserTimeZone(requesterTimeZone).ToShortDateString()),
                                     new XAttribute("ForumCategoryId", t1.ForumCategory.Id),
                                     from p in t1.ForumPost
                                     where t1.ForumPost.Max(t2 => t2.PostMessage.CreationDate) == p.PostMessage.CreationDate
                                     select new XElement("LastPost",
                                         new XAttribute("LastPostDate", p.PostMessage.CreationDate.ToUserTimeZone(requesterTimeZone).ToString()),
                                         new XAttribute("LastPostAuthor", p.PostMessage.Author),
                                         new XAttribute("LastPostTopicId", p.ForumTopic.Id.ToString()),
                                         new XAttribute("LastPostId", p.Id.ToString()),
                                         new XAttribute("ForumCategoryId", t1.ForumCategory.Id)
                                         )
                                         ));
                    xelem = new XElement("o", query);
                }
            }
            catch (Exception ex)
            {
                LoggingManager.Logger.Log(LoggingCategory.Error, ex.Message);
                throw new DalException(String.Format("RetrieveError".ToLocalizedString(), "GetXmlEntities", ex.Message), ex) { EntityType = typeof(Forum[]) };
            }
            return this.xmlResultToDataSet(xelem.ToString());
        }

        // Topic non lu par currentuser => méthode interdite au utilisateur anonyme
        public DataSet GetUnreadXmlEntities(String AnonymousID, String requesterTimeZone)
        {
            if (AnonymousID != String.Empty)
                return null;

            XElement xelem = null;

            try
            {
                Guid UserKey = Guid.Parse(Membership.GetUser(false).ProviderUserKey.ToString());
                List<Int32> ForumIdAllowed = UserAccessDal.ForumIdAllowed(String.Empty);

                using (FranceTriple.Dal.Model1Container ctx = new Model1Container())
                {
                    // Tous les messages autorisés non-lu UNION les sujets partiellement lus
                    var TopicWithPostToRead =
                            (from topic in
                                 (from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumPost.ForumPostRead")
                                  where ForumIdAllowed.Contains(t.ForumCategory.Forum.Id)
                                  select t)
                             join readTopic in
                                 (from read in ctx.ForumPostReadSet.Include("ForumPost").Include("ForumPost.ForumTopic")
                                  group read by new { read.ForumPost.ForumTopic.Id, read.UserId } into Group
                                  where Group.Key.UserId.CompareTo(UserKey) == 0
                                  select new { Id = Group.Key.Id, ReadCount = Group.Count() })
                             on topic.Id equals readTopic.Id
                             into readTopicResult
                             where readTopicResult.Count() == 00
                             select topic).Union(
                             from topic in
                                 (from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumPost.ForumPostRead")
                                  where ForumIdAllowed.Contains(t.ForumCategory.Forum.Id)
                                  select new { Id = t.Id, PostCount = t.ForumPost.Count(), Topic = t })
                             join readTopic in
                                 (from read in ctx.ForumPostReadSet.Include("ForumPost").Include("ForumPost.ForumTopic")
                                  group read by new { read.ForumPost.ForumTopic.Id, read.UserId } into Group
                                  where Group.Key.UserId.CompareTo(UserKey) == 0
                                  select new { Id = Group.Key.Id, ReadCount = Group.Count() })
                             on topic.Id equals readTopic.Id
                             where (readTopic.ReadCount == 0) || (topic.PostCount - readTopic.ReadCount > 0)
                             select topic.Topic);

                    var TargetTopic = ((from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumCategory")
                                        select t).Intersect(TopicWithPostToRead)).ToList();

                    var query = (from t1 in TargetTopic
                                 where t1.ForumPost.Count > 0
                                 orderby t1.OrderForumTopic descending, t1.ForumPost.Max(po => po.PostMessage.CreationDate) descending
                                 select new XElement("Topic",
                                     new XAttribute("Id", t1.Id),
                                     new XAttribute("Label", t1.LabelForumTopic),
                                     new XAttribute("NumberOfPost", t1.ForumPost.Count),
                                     new XAttribute("Author", t1.AuthorForumTopic),
                                     new XAttribute("NumberHasBeenSeen", t1.NumberHasBeenSeenForumTopic),
                                     new XAttribute("IsUnlocked", t1.IsUnlocked),
                                     new XAttribute("OrderForumTopic", t1.OrderForumTopic),
                                     new XAttribute("CreationDateForumTopic", t1.CreationDateForumTopic.ToUserTimeZone(requesterTimeZone).ToShortDateString()),
                                     new XAttribute("ForumCategoryId", t1.ForumCategory.Id),
                                     from p in t1.ForumPost
                                     where (t1.ForumPost.Max(t2 => t2.PostMessage.CreationDate) == p.PostMessage.CreationDate) && (p.PostMessage != null)
                                     select new XElement("LastPost",
                                         new XAttribute("LastPostDate", p.PostMessage.CreationDate.ToUserTimeZone(requesterTimeZone).ToString()),
                                         new XAttribute("LastPostAuthor", p.PostMessage.Author),
                                         new XAttribute("LastPostTopicId", p.ForumTopic.Id.ToString()),
                                         new XAttribute("LastPostId", p.Id.ToString()),
                                         new XAttribute("ForumCategoryId", t1.ForumCategory.Id)
                                         )
                                         ));



                    xelem = new XElement("o", query);
                }
            }
            catch (Exception ex)
            {
                LoggingManager.Logger.Log(LoggingCategory.Error, ex.Message);
                throw new DalException(String.Format("RetrieveError".ToLocalizedString(), "GetXmlEntities", ex.Message), ex) { EntityType = typeof(Forum[]) };
            }
            return this.xmlResultToDataSet(xelem.ToString());
        }

        // Recherche par mot-clefs et nom d'auteurs.
        public DataSet SearchByKeywordsAndAuthors(String Keywords, String[] Authors, String AnonymousID, String requesterTimeZone)
        {
            XElement xelem = null;
            String SearchedAuthors = String.Join(" ", Authors);

            try
            {
                using (FranceTriple.Dal.Model1Container ctx = new Model1Container())
                {
                    List<Int32> ForumIdAllowed = UserAccessDal.ForumIdAllowed(AnonymousID);

                    var resultId = (from t in ctx.ForumTopicSet.Include("ForumPost")
                                    where ForumIdAllowed.Contains(t.ForumCategory.Forum.Id)
                                    & (t.LabelForumTopic.Contains(Keywords) |
                                    t.ForumPost.Any(p => p.PostMessage.Label.Contains(Keywords)) |
                                    t.ForumPost.Any(p => p.PostMessage.Message.Contains(Keywords)))
                                    & (SearchedAuthors == String.Empty || t.ForumPost.Any(p => SearchedAuthors.Contains(p.PostMessage.Author)))
                                    select t.Id);

                    var query = (from t1 in
                                     (from t in ctx.ForumTopicSet.Include("ForumPost").Include("ForumCategory")
                                      where resultId.Contains(t.Id)
                                      orderby t.OrderForumTopic descending, t.CreationDateForumTopic descending
                                      select t).ToList()
                                 select new XElement("Topic",
                                     new XAttribute("Id", t1.Id),
                                     new XAttribute("Label", t1.LabelForumTopic),
                                     new XAttribute("NumberOfPost", t1.ForumPost.Count),
                                     new XAttribute("Author", t1.AuthorForumTopic),
                                     new XAttribute("NumberHasBeenSeen", t1.NumberHasBeenSeenForumTopic),
                                     new XAttribute("IsUnlocked", t1.IsUnlocked),
                                     new XAttribute("OrderForumTopic", t1.OrderForumTopic),
                                     new XAttribute("CreationDateForumTopic", t1.CreationDateForumTopic.ToUserTimeZone(requesterTimeZone).ToShortDateString()),
                                     new XAttribute("ForumCategoryId", t1.ForumCategory.Id),
                                     from p in t1.ForumPost
                                     where t1.ForumPost.Max(t2 => t2.PostMessage.CreationDate) == p.PostMessage.CreationDate
                                     select new XElement("LastPost",
                                         new XAttribute("LastPostDate", p.PostMessage.CreationDate.ToUserTimeZone(requesterTimeZone).ToString()),
                                         new XAttribute("LastPostAuthor", p.PostMessage.Author),
                                         new XAttribute("LastPostTopicId", p.ForumTopic.Id.ToString()),
                                         new XAttribute("LastPostId", p.Id.ToString()),
                                         new XAttribute("ForumCategoryId", t1.ForumCategory.Id)
                                                        )
                                         ));
                    xelem = new XElement("o", query);
                }
            }
            catch (Exception ex)
            {
                LoggingManager.Logger.Log(LoggingCategory.Error, ex.Message);
                throw new DalException(String.Format("RetrieveError".ToLocalizedString(), "GetXmlEntities", ex.Message), ex) { EntityType = typeof(Forum[]) };
            }
            return this.xmlResultToDataSet(xelem.ToString());
        }

        private DataSet xmlResultToDataSet(String xmlResult)
        {
            DataSet ds = new DataSet();
            System.IO.StringReader xmlSR = null;
            xmlSR = new System.IO.StringReader(xmlResult);
            ds.ReadXml(xmlSR);
            return ds;
        }
    }
}
